<?php
date_default_timezone_set('prc');
class Article
{ 
    const __default=self::Company;
    const Company = '公司介绍';
    const Service = '服务内容介绍';
    const Achievement = '业绩考核文档';
    const Activity = '公司活动、政策';
    const Inform = '公司通知';
    const Caption = '操作说明文档';
}
class Status
{
    const __default = self::Normal;
    const Normal = '正常';
    const NonUse = '禁用';
    const Delete = '删除';
    const Finish = '完成';
}

class Role
{
    const __default = self::Ordinary;
    const Ordinary = '普通';
    const Manager = '管理员';
    const SuperManager = '超级管理员';
}

class Core
{
    private $dns = "mysql:host=ylhsdb.mysqldb.chinacloudapi.cn;dbname=ylhs_db;charset=utf8;";
    private $user = "ylhsdb%lchroot";
    private $password = "Password01!";
//    private $dns = "mysql:host=127.0.0.1;dbname=ylhs_db;charset=utf8;";
//    private $user = "root";
//    private $password = "root";
    private $pdo;

    function __construct()
    {
        try {
            $this->pdo = new PDO($this->dns, $this->user, $this->password);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (Exception $e) {
            echo 'Connection failed: ' . $e->getMessage();
        }
    }

    /**
     * 增加一个用户
     * @param $wxid 微信Id
     * @param $phone  手机号
     * @param $pid  推荐人Id
     * @param string $icon 头像
     * @param string $name 姓名
     * @return string  为 空的时候 说明添加成功 否则 为错误消息
     */
    function AddUser($wxid, $phone, $pid, $icon = "", $name = "")
    {
        $sql = "INSERT INTO user (weixin_Id,icon,name,phone,bonus,remain_bonus,expend,parent_Id,create_time,status) VALUES (:wxid ,:icon ,:name ,:phone ,0,0,0,:pid,now(),:status);";
        $result = $this->execSetSql(
            $sql,
            array("wxid" => $wxid, "icon" => $icon, "name" => $name, "phone" => $phone, "pid" => $pid, "status" => Status::Normal)
        );
        if (strpos($result, $wxid)) return "微信号已经绑定过了！";
        return $result;
    }

    /**
     * 执行sql插入语句
     * @param $sql sql执行语句
     * @param $arr sql参数数组
     * @return string为 空的时候 说明执行成功 否则 为错误消息
     */
    private function execSetSql($sql, $arr)
    {
        try {
            $prepare = $this->pdo->prepare($sql);
            $sqlOk = $prepare->execute($arr);
            if ($sqlOk) {
                if (!($prepare->rowCount() > 0)) {
                    throw new Exception("操作失败！未影响数据");
                }
            } else {
                throw new Exception("操作失败！");
            }
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }

    /**
     * 获取所有状态为正常的用户
     * @param int $page 第几页 从1开始
     * @return array 返回所有状态为正常的用户 或者空数组
     */
    function GetNormalUsers($page = 1)
    {
        return $this->GetUsers(Status::Normal, $page);
    }

    /**
     * 获取满足状态的所有用户
     * @param $status 用户的状态
     * @param int $page 要查找的页数
     * @return array 所有满足条件的用户数据
     */
    private function GetUsers($status, $page = 1)
    {
        $sql = "SELECT * FROM user WHERE status='" . $status . "' LIMIT " . (20 * ($page - 1)) . ",20";
        return $this->execGetAll($sql);
    }

    /**
     * 查询多条数据
     * @param $sql sql执行语句
     * @param array $arr sql参数数组
     * @return array 查询到的数据
     */
    private function execGetAll($sql, $arr = array())
    {
        $prepare = $this->pdo->prepare($sql);
        $sqlOk = $prepare->execute($arr);
        return $sqlOk ? $prepare->fetchAll(PDO::FETCH_NAMED) : array();
    }

    /**
     *获取正常用户总数量
     * @return mixed 正常用户总数量
     */
    function GetNormalUsersCount()
    {
        return $this->GetUsersCount(Status::Normal);
    }

    /**
     * 获取某种状态的用户数量
     * @param $status 用户状态
     * @return mixed  用户数量
     */
    private function GetUsersCount($status){
        $sql="SELECT COUNT(*) AS count FROM user WHERE status='".$status."'";
        $result=$this->execGetFirst($sql,array());
        return $result["count"];
    }

    /**
     * sql中查找满足条件的第一条数据
     * @param $sql sql执行语句
     * @param $arr sql参数数组
     * @return array|mixed 第一条查找到的数据 或者空数组
     */
    private function execGetFirst($sql, $arr)
    {
        $prepare = $this->pdo->prepare($sql);
        $sqlOk = $prepare->execute($arr);
        return $sqlOk ? $prepare->fetch(PDO::FETCH_NAMED) : array();
    }

    /**
     * 获取所有状态为禁用的用户
     * @param int|第几页 $page 第几页 从1开始
     * @return array 返回所有状态为禁用的用户 或者空数组
     */
    function GetNonUseUsers($page=1){
        return $this->GetUsers(Status::NonUse,$page);
    }

    /**
     *获取禁用用户总数量
     * @return mixed 禁用用户总数量
     */
    function GetNonUseUsersCount(){
        return $this->GetUsersCount(Status::NonUse);
    }

    /**
	 * 通过微信查找用户
     * @param $wxId 微信号
     * @return array|mixed 第一条查找到的用户数据 或者空数组
     */
    function GetUserByWxId($wxId)
    {
        $sql = "SELECT * FROM user WHERE weixin_Id=? AND status='" . Status::Normal . "'";
        return $this->execGetFirst($sql, array($wxId));
    }



    //执行 增删改 sql

    /**
     * 通过用户ID查找子用户
     * @param $wxId 用户ID
     * @return array 返回下一级所有用户  或者空数组
     */
    function GetChildUsersById($id){
        $sql = "SELECT * FROM user WHERE parent_Id=? AND status='" . Status::Normal . "'";
        return $this->execGetAll($sql, array($id));
    }

    //执行 获取一条返回 数据

    /**
	 * 编辑用户资料
     * @param $userId 被编辑用户ID
     * @param $phone 新的手机号
     * @param $icon 新的照片信息
     * @param $name 新的名称
     * @return string 为空 说明编辑成功 否则返回错误信息
     */
    function UpdateUser($userId, $phone, $icon, $name)
    {
        $user = $this->GetUserById($userId);
		if(empty($user))return "没有该用户";
        $user["phone"] = $phone;
        $user["icon"] = $icon;
        $user["name"] = $name;
        return $this->SetUser($user);
    }

    /**
	 * 通过ID查找用户
     * @param $userId 用户ID
     * @return array|mixed 一条用户数据 或者空数组
     */
    function GetUserById($userId)
    {
        $sql = "SELECT * FROM user WHERE id=?";
        return $this->execGetFirst($sql, array($userId));
    }

    /**
     * 修改用户数据
     * @param $user 完整用户数据数组
     * @return string 为空 说明执行成功 否则返回错误信息
     */
    private function SetUser($user)
    {
        $sql = "UPDATE user SET 
                      icon=:icon,
                      name=:name,
                      phone=:phone,
                      bonus=:bonus,
                      remain_bonus=:remain_bonus,
                      expend=:expend,
                      status=:status,
                      weixin_Id=:weixin_Id,
                      create_time=:create_time,
                      parent_Id=:parent_Id 
                  WHERE id=:id;";
        $result = $this->execSetSql($sql, $user);
        return $result;
    }

    /**
	 * 设置用户状态为正常
     * @param $userId 用户ID
     * @return string 为空 说明执行成功 否则返回错误信息
     */
    function OpenUser($userId)
    {
        $user = $this->GetUserById($userId);
		if(empty($user))return "没有该用户";
        SendMessageToOpenId($user["weixin_Id"],"你的乐巢会会员已经启用" );
        $user["status"] = Status::Normal;
        return $this->SetUser($user);
    }

    /**
	 * 设置用户状态为禁用
     * @param $userId 用户ID
     * @return string 为空 说明执行成功 否则返回错误信息
     */
    function CloseUser($userId)
    {
        $user = $this->GetUserById($userId);
		if(empty($user))return "没有该用户";
        SendMessageToOpenId($user["weixin_Id"],"你的乐巢会会员已经被禁用了" );
        $user["status"] = Status::NonUse;
        return $this->SetUser($user);
    }

    /**
	 * 生成消费记录
     * @param $userId 用户ID
     * @param $orderNum 订单号
     * @param $money 消费金额
     * @param $isBenefit 是否已优惠
     * @param $remark 备注
     * @return string 为空 说明执行成功 否则返回错误信息
     */
    function UserExpend($userId, $orderNum, $money, $isBenefit, $remark)
    {
        //修改用户总消费
        $this->UpdateUserExpend($userId, $money);
        $ratio1=$this->GetRatioByRank("一级");
        $ratio2=$this->GetRatioByRank("二级");
        $ratio3=$this->GetRatioByRank("三级");
        $bonus1=(float)$money*(float)$ratio1["ratio"]/100;
        $bonus2=(float)$money*(float)$ratio2["ratio"]/100;
        $bonus3=(float)$money*(float)$ratio3["ratio"]/100;

        if(!empty($parent1=$this->GetParentById($userId))){
            //$this->UpdateUserBonus($parent1["id"],$bonus1);
            $this->AddUserBonus($parent1["id"], $bonus1, $userId);
            if(!empty($parent2=$this->GetParentById($parent1["id"]))){
                //$this->UpdateUserBonus($parent2["id"],$bonus2);
                $this->AddUserBonus($parent2["id"], $bonus2, $userId);
                if(!empty($parent3=$this->GetParentById($parent2["id"]))){
                    //$this->UpdateUserBonus($parent3["id"],$bonus3);
                    $this->AddUserBonus($parent3["id"], $bonus3, $userId);
                }
            }
        }

        //todo:发送微信通知
        SendMessage($userId,"尊敬的会员，您好！您于".date('Y年m月d日 H时i分s秒')."在乐巢会共消费金额".$money."元，如有疑问，请与前台客服联系");


        //用户消费记录
        $allowBarterPrize = false;//是否可以兑换礼物
        $prizes = $this->GetPrizes();
        foreach ($prizes as $v) {
            if ($money >= $v["expend"]) {
                $allowBarterPrize = true;
                break;
            }
        }
        return $this->AddExpend($userId, $orderNum, $money, $isBenefit, $remark, $allowBarterPrize);


    }

    /**
	 * 修改用户总消费
     * @param $userId 用户ID
     * @param $money 本次消费金额
     * @return string 为空 说明执行成功 否则返回错误信息
     */
    private function UpdateUserExpend($userId, $money)
    {
        if ($money < 0) return "消费不能负";
        $user = $this->GetUserById($userId);
		if(empty($user))return "没有该用户";
        $user["expend"] += $money;
        return $this->SetUser($user);
    }

    /**
     * 按分佣层级获取
     * @param $rank 分佣层级
     * @return array|mixed 为空的时候 说明查询成功 否则为错误信息
     */
    function GetRatioByRank($rank)
    {
        $sql = "select * from bonus_ratio where rank=:rank";
        return $this->execGetFirst($sql, array("rank" => $rank));
    }

    //array("icon"=>"",name=>"asdfas",...)

    /**
     * 通过用户ID 查找上级用户
     * @param $user_id 用户ID
     * @return array|mixed 上级用户
     */
    function GetParentById($user_id)
    {
        $parent_id = $this->GetUserById($user_id)["parent_Id"];
        return $this->GetUserById($parent_id);
    }

    /**
     * 更改用户总佣金
     * @param $userId 用户ID
     * @param $bonus 要加上的佣金
     * @return string 为空 说明执行成功 否则返回错误信息
     */
    private function UpdateUserBonus($userId, $bonus)
    {
        if ($bonus <= 0) return "佣金不能为负";
        $user = $this->GetUserById($userId);
        $user["bonus"] += $bonus;
        $user["remain_bonus"] += $bonus;
        return $this->SetUser($user);
    }

    /**
     * 生成个人佣金记录
     * @param $userId 用户Id
     * @param $money 增加的佣金
     * @param $remark 备注
     * @return string 为空的时候 说明修改成功 否则 为错误消息
     */
    function AddUserBonus($userId, $money, $remark)
    {
        $this->UpdateUserBonus($userId, $money);//修改佣金;

        $sql = "INSERT INTO bonus_log (user_id,money,create_time,remark) VALUES (:user_id ,:money,now(),:remark)";
        $result = $this->execSetSql(
            $sql,
            array("user_id" => $userId, "money" => $money, "remark" => $remark)
        );
        return $result;
    }

    /**
	 * 获取所有奖品数据
     * @return array 所有奖品数据
     */
    function GetPrizes()
    {
        $sql = "select * from prize WHERE status='" . Status::Normal . "' ORDER BY expend ASC";
        return $this->execGetAll($sql);
    }

    function GetAllPrizes(){
        $sql = "select * from prize ORDER BY status desc,expend ASC";
        return $this->execGetAll($sql);
    }

    /**
     * 添加消费记录
     * @param $userId 用户ID
     * @param $orderNum 订单号
     * @param $money 消费金额
     * @param $isBenefit 是否已优惠
     * @param $remark 备注
     * @param $allowBarter 有无可兑换礼品
     * @return string 为空 说明执行成功 否则返回错误信息
     */
    private function AddExpend($userId, $orderNum, $money, $isBenefit, $remark, $allowBarter)
    {
        $sql = "INSERT INTO `expend_log` SET 
                    `order_num`=:order_num,
                    `money`=:money,
                    `create_time`=now(),
                    `user_id`=:user_id,
                    `is_benefit`=:is_benefit,
                    `remark`=:remark,
                    `allow_barter`=:allow_barter,
                    `finish_barter`=:finish_barter;";
        return $this->execSetSql(
            $sql,
            array(
                "order_num" => $orderNum,
                "money" => $money,
                "user_id" => $userId,
                "is_benefit" => $isBenefit,
                "remark" => $remark,
                "allow_barter" => $allowBarter,
                "finish_barter" => false
            )
        );
    }

    /**
     * 添加奖品
     * @param $name 奖品名
     * @param $expend 需要总消费金额
     * @param $remark 备注
     * @param string $status
     * @return string 为空 说明执行成功 否则返回错误信息
     */
    function AddPrize($name, $expend, $remark,$status=Status::Normal)
    {
        if($status!=Status::Normal){
            $status=Status::Delete;
        }
        $sql = "INSERT INTO prize(name,expend,remark,status) VALUES(:name,:expend,:remark,'" . $status . "')";
        return $this->execSetSql(
            $sql,
            array(
                "name" => $name,
                "expend" => $expend,
                "remark" => $remark
            )
        );
    }




    //通过ID查询奖品

    /**
	 * 通过奖品ID查询奖品
     * @param $id 奖品ID
     * @return array|mixed 一条奖品数据
     */
    function GetPrizeById($id)
    {
        $sql = "SELECT * FROM prize WHERE id=:id";
        return $this->execGetFirst($sql, array("id" => $id));
    }

    //删除奖品

    /**
	 * 设置奖品状态为删除
     * @param $id 奖品ID
     * @return string 为空 说明执行成功 否则返回错误信息
     */
    function DelPrize($id)
    {
        $sql = "UPDATE prize SET status='" . Status::Delete . "' WHERE id=:id";
        return $this->execSetSql(
            $sql,
            array(
                "id" => $id
            )
        );
    }

    //编辑奖品

    /**
     * 编辑奖品信息
     * @param $id 被编辑奖品ID
     * @param $name 新的奖品名称
     * @param $expend 新的兑换奖品需要满足的总消费额
     * @param $remark 新的备注
     * @param string $status
     * @return string 为空 说明执行成功 否则返回错误信息
     */
    function UpdatePrize($id, $name, $expend, $remark,$status=Status::Normal)
    {
        if($status!=Status::Normal){
            $status=Status::Delete;
        }
        $sql = "UPDATE prize SET name=:name,expend=:expend,remark=:remark,status='".$status."' WHERE id=:id";
        return $this->execSetSql(
            $sql,
            array(
                "name" => $name,
                "expend" => $expend,
                "remark" => $remark,
                "id" => $id
            )
        );
    }

    /**
	 * 通过用机号查询用户
     * @param $phone 手机号
     * @return array|mixed 一条用户数据 或者空数组
     */
    function GetUserByPhone($phone)
    {
        $sql = "SELECT * FROM user WHERE phone=:phone AND status='" . Status::Normal . "'";
        return $this->execGetFirst($sql, array("phone" => $phone));
    }

    //通过微信号，用户名，手机号 模糊搜索用户

    /**
     * 模糊搜索用户
     * @param $search 搜索字段  对 微信号 用户名 手机号 进行匹配
     * @param int $page 页数从1开始 20个每页
     * @return array 搜索到的所有用户数据
     */
    function GetUserBySearch($search,$page=1)
    {
        return $this->getUsersBySearch(Status::Normal,$search,$page);
    }

    //更改消费

    /**
     * 模糊搜索用户
     * @param $status 用户状态
     * @param $search 搜索字段  对 微信号 用户名 手机号 进行匹配
     * @param int $page 页数从1开始 20个每页
     * @return array 搜索到的所有用户数据
     */
    private function getUsersBySearch($status, $search, $page = 1)
    {
        $sql = "SELECT * FROM user WHERE (weixin_Id like :weixin_Id OR name LIKE :name OR phone LIKE :phone) AND status='" . $status . "' LIMIT " . (20 * ($page - 1)) . ",20";
        return $this->execGetAll(
            $sql,
            array(
                "weixin_Id" => "%" . trim($search) . "%",
                "name" => "%" . trim($search) . "%",
                "phone" => "%" . trim($search) . "%"
            )
        );
    }

    /**
     * 模糊搜索被禁用的用户
     * @param $search 搜索字段  对 微信号 用户名 手机号 进行匹配
     * @param int $page 页数从1开始 20个每页
     * @return array 搜索到的所有用户数据
     */
    function GetNonUseUserBySearch($search, $page=1){
        return $this->getUsersBySearch(Status::NonUse,$search,$page);
    }


    /**
     * 获取搜索到的禁用用户的数量
     * @param $search 搜索关键字
     * @return mixed 搜索到的用户数量
     */
    function GetNonUseUserBySearchCount($search)
    {
        return $this->getUsersBySearchCount(Status::NonUse,$search);
    }

    /**
     * 获取搜索到用户的信息数量
     * @param $status 用户状态
     * @param $search 搜索关键字
     * @return mixed 搜索到的用户数量
     */
    private function getUsersBySearchCount($status, $search)
    {
        $sql = "SELECT COUNT(*) AS count FROM user WHERE (weixin_Id like :weixin_Id OR name LIKE :name OR phone LIKE :phone) AND status='" . $status . "'";
        return $this->execGetFirst(
            $sql,
            array(
                "weixin_Id" => "%" . trim($search) . "%",
                "name" => "%" . trim($search) . "%",
                "phone" => "%" . trim($search) . "%"
            )
        )["count"];
    }


    //更改佣金

    /**
     * 获取要搜索用户的数量
     * @param $search 搜索关键字
     * @return mixed  数量
     */
    function GetUserBySearchCount($search)
    {
        return $this->getUsersBySearchCount(Status::Normal, $search);
    }

    //提现

    /**
	 * 绑定银行卡
     * @param $user_id  用户ID
     * @param $card_num  银行卡号
     * @param $holder  户主名称
     * @param string $bank_name 开户行
     * @return string 为空 说明执行成功 否则返回错误信息
     */
    function AddCard($user_id, $card_num, $holder, $bank_name)
    {
        $cards = $this->GetCardsByUserId($user_id);
        foreach ($cards as $card) {
            if ($card["card_num"] == $card_num) return "银行卡已经绑定过了";
        }
        $sql = "INSERT INTO card(bank_name,card_num,holder,user_id,create_time,status) VALUES(:bn,:cn,:hr,:ud,now(),:sts)";
        return $this->execSetSql($sql, array("bn" => $bank_name, "cn" => $card_num, "hr" => $holder, "ud" => $user_id,"sts"=>Status::Normal));
    }

    /**
	 * 通过用户ID查询已经绑定该用户的所有银行卡
     * @param $user_id 用户ID
     * @return array 银行卡数据 或者空数组
     */
    function GetCardsByUserId($user_id)
    {
        $sql = "SELECT * FROM card WHERE user_id=:ud AND status='".Status::Normal."'";
        return $this->execGetAll($sql, array("ud" => $user_id));
    }

    //通过用户ID查询银行卡

    /**
     * 编辑银行卡
     * @param $id 被编辑id
     * @param $user_id 用户id
     * @param $card_num 银行卡号
     * @param $holder 户主名称
     * @param string|开户行名称 $bank_name 开户行名称
     * @return string 为空 说明执行成功 否则返回错误信息
     */
    function SetCard($id, $user_id, $card_num, $holder, $bank_name)
    {
        $cards = $this->GetCardsByUserId($user_id);
        foreach ($cards as $card) {
            if ($card["card_num"] == $card_num && $card["id"] != $id) return "银行卡已经绑定过了";
        }
        $sql = "UPDATE card SET bank_name=:bn,card_num=:cn,holder=:hr WHERE id=:id";
        return $this->execSetSql($sql, array("bn" => $bank_name, "cn" => $card_num, "hr" => $holder, "id" => $id));
    }


    //绑定银行卡

    /**
	 * 删除银行卡
     * @param $id id
     * @return string 为空 说明执行成功 否则返回错误信息
     */
    function DelCard($id)
    {
        $card=$this->GetCardById($id);
        $sql = "UPDATE card SET status='".Status::Delete."' WHERE id=:id";
        $err=$this->execSetSql($sql, array("id" => $id));
        if(empty($err)){
            $this->DelWithdraw($card["user_id"],$card["id"]);
        }
        return $err;
    }

    //编辑银行卡

    /**
     * 通过ID搜索银行卡
     * @param $id id
     * @return array|mixed 搜索到的第一条银行卡数据 或者空数组
     */
    function GetCardById($id)
    {
        $sql = "SELECT * FROM card WHERE id=:id";
        return $this->execGetFirst($sql, array("id" => $id));
    }

    /**
     * 删除用户提现申请
     * @param $user_id 用户ID
     * @param $card_id 银行卡ID
     * @return string为  为空的时候 说明申请成功 否则 为错误消息
     */
    private function DelWithdraw($user_id, $card_id)
    {
        $sql = "delete from withdraw_log where user_id=:ud and card_id=:cd";
        return $this->execSetSql($sql, array("ud" => $user_id, "cd" => $card_id));
    }

    /**
     * 用户提现申请;
     * @param $userId 用户Id
     * @param $money 申请提现金额
     * @param $card_id 银行卡ID
     * @return string 为空的时候 说明申请成功 否则 为错误消息
     */
    function SetUserWithdraw($userId, $money,$card_id)
    {
        $user = $this->GetUserById($userId);
        $userBonus = $user["remain_bonus"];
        if ($userBonus >= $money) {
            $sql = "INSERT INTO withdraw_log (user_id,money,create_time,status,card_id) VALUES (:user_id ,:money,now(),:status,:card_id);";
            $result = $this->execSetSql(
                $sql,
                array("user_id" => $userId, "money" => $money, "status" => Status::Normal,"card_id"=>$card_id)
            );
        } else {
            return "账户佣金不足，申请失败！";
        }
        return $result;
    }

    /**
     * 通过用户Id查询用户提现申请;
     * @param $userId 用户Id
     * @return array|mixed 为空的时候 说明查询成功 否则 为错误消息
     */
    function GetUserWithdrawByUserId($userId)
    {
        $sql = "SELECT * FROM withdraw_log WHERE user_id=? and status='".Status::Normal."'";
        return $this->execGetAll($sql, array($userId));
    }

    /**
     * 通过用户Id查询用户提现记录;
     * @param $userId 用户Id
     * @return array 为空的时候 说明查询成功 否则 为错误消息
     */
    function GetAllUserWithdrawByUserId($userId)
    {
        $sql = "SELECT * FROM withdraw_log WHERE user_id=?";
        return $this->execGetAll($sql, array($userId));
    }

    /**
     * 查询所有提现申请;
     * @return array 为空的时候 说明查询成功 否则 为错误消息
     */
    function GetAllWithdraw()
    {
        $sql = "select * from withdraw_log;";
        return $this->execGetAll($sql);
    }

    /**
     * 模糊查找已完成的用户的提现申请列表
     * @param string $search 关键字
     * @return array 提现申请记录
     */
    function GetFinishWithdraw($search = "")
    {
        return $this->GetWithdrawAndUser($search, Status::Finish);
    }

    function GetFinishWithdrawWhen($when)
    {
        $when=$this->WhenToWhen($when,"a.");
        $sql = "select
b.weixin_Id,b.name,b.phone,a.money,a.create_time
from withdraw_log a
LEFT JOIN user b
            ON a.user_id=b.id
            WHERE ".$when." AND a.status='".Status::Finish."' order by a.create_time asc";
        return $this->execGetAll($sql);
    }

    function GetFinishWithdrawWhenTotal($when)
    {
        $when=$this->WhenToWhen($when,"a.");
        $sql = "select
sum(a.money) as total_money
from withdraw_log a
LEFT JOIN user b
            ON a.user_id=b.id
            WHERE ".$when." AND a.status='".Status::Finish."'";
        return $this->execGetFirst($sql,array());
    }




    /**
     * 模糊查找某状态的用户的提现申请列表
     * @param $search 关键字
     * @param $status 状态  正常 或者 完成
     * @return array 提现申请列表
     */
    private function GetWithdrawAndUser($search, $status){
        $sql = "select * from 
            withdraw_log a LEFT JOIN user b 
            ON a.user_id=b.id 
            WHERE (b.weixin_Id like :weixin_Id OR b.name LIKE :name OR b.phone LIKE :phone) 
            AND a.status='".$status."' 
            AND b.status='".Status::Normal."'";
        return $this->execGetAll(
            $sql,
            array(
                "weixin_Id" => "%" . trim($search) . "%",
                "name" => "%" . trim($search) . "%",
                "phone" => "%" . trim($search) . "%"
            )
        );
    }

    /**
     * 模糊查找正常状态的用户的提现申请列表
     * @param string $search 关键字
     * @return array 提现申请列表
     */
    function GetNormalWithdraw($search=""){
        return $this->GetWithdrawAndUser($search,Status::Normal);
    }


    /**
     *完成提现,修改状态为完成;
     * @param $userId 用户Id
     * @param $id 提现申请Id
     * @return string 为空的时候 说明修改成功 否则 为错误消息
     */
    function UpdateWithdraw($userId, $id)
    {
        $UserWithdraw = $this->GetUserWithdrawById($id);
        if($UserWithdraw["status"] == Status::Normal){
            $result = $this->UpdateUserRemain_Bonus($userId,$UserWithdraw["money"]);
            if (empty($result)) {
                $UserWithdraw["status"] = Status::Finish;
                SendMessage($userId,"尊敬的会员，您好！您于".$UserWithdraw['create_time']."申请的提现已完成，感谢您的使用！如有疑问，请与客服联系");
                return $this->updateUserWithdraw($UserWithdraw);
            }else{
                return $result;
            }
        }
    }

    /**
     * 通过提现Id查询用户提现申请;
     * @param $id 提现Id
     * @return array|mixed 为空的时候 说明查询成功 否则 为错误消息
     */
    function GetUserWithdrawById($id)
    {
        $sql = "SELECT * FROM withdraw_log WHERE id=?";
        return $this->execGetFirst($sql, array($id));
    }

    /**
	 * 更改用户可提现金额
     * @param $userId 用户ID
     * @param $remain_bonus 要减去的金额
     * @return string 为空 说明执行成功 否则返回错误信息
     */
    function UpdateUserRemain_Bonus($userId, $remain_bonus)
    {
        if ($remain_bonus <= 0) return "提现金额不能为负";
        $user = $this->GetUserById($userId);
        if (($user["remain_bonus"] -= $remain_bonus) < 0) return "余额不足";
        return $this->SetUser($user);
    }

    /**
     * 修改提现申请;
     * @param $UserWithdraw 修改后的提现申请;
     * @return string 为空的时候 说明修改成功 否则 为错误消息
     */
    private function updateUserWithdraw($UserWithdraw)
    {
        $sql = "UPDATE withdraw_log SET
                      user_id=:user_id,
                      money=:money,
                      create_time=:create_time,
                      status=:status,
                      card_id=:card_id
                  WHERE id=:id;";
        $result = $this->execSetSql($sql, $UserWithdraw);
        return $result;
    }

    /**查询用户佣金记录
     * @param $userId 用户Id
     * @return array|mixed 为空的时候 说明查询成功 否则 为错误消息
     */
    function getUserBonus($userId)
    {
        $sql = "SELECT * FROM bonus_log WHERE user_id=?";
        return $this->execGetAll($sql, array($userId));
    }

    /**
     * 从佣金日志获取用户当月得到佣金总数
     * @param $user_id 用户ID
     * @return mixed  当月得到佣金
     */
    function GetUserMonthBonusById($user_id){
        $sql="select SUM(money) AS sum from bonus_log where date_format(create_time,'%Y-%m')=date_format(now(),'%Y-%m') AND user_id=? ";
        return $this->execGetFirst($sql, array($user_id))["sum"];
    }

    /**
     * 从佣金日志获取所有用户当月得到佣金总数
     * @return mixed 所有用户当月得到佣金总数
     */
    function GetAllMonthBonusSUM(){
        $sql="select SUM(money) AS sum from bonus_log where date_format(create_time,'%Y-%m')=date_format(now(),'%Y-%m')";
        return $this->execGetFirst($sql,array())["sum"];
    }

    /**
     * 从佣金日志获取所有用户总佣金
     * @return mixed 所有用户总佣金
     */
    function GetAllBonusSUM(){
        $sql="select SUM(money) AS sum from bonus_log";
        return $this->execGetFirst($sql,array())["sum"];
    }

    /**
     * 从消费日志获取当月所有用户总消费
     * @return mixed 当月总消费
     */
    function GetAllMonthExpendSUM(){
        $sql="select SUM(money) AS sum from expend_log where date_format(create_time,'%Y-%m')=date_format(now(),'%Y-%m')";
        return $this->execGetFirst($sql,array())["sum"];
    }

    /**
     * 从消费日志获取所有用户总消费
     * @return mixed 总消费
     */
    function GetAllExpendSUM(){
        $sql="select SUM(money) AS sum from expend_log";
        return $this->execGetFirst($sql,array())["sum"];
    }

    /**
     * 联合查询佣金日志与用户 获取用户所得佣金的来源用户信息
     * @param $user_id  获得佣金 用户信息
     * @return array  佣金信息及来源用户信息
     */
    function GetUserBonusAndFromUser($user_id){
        $sql = "SELECT a.*,b.name FROM bonus_log a LEFT JOIN user b ON a.remark=b.id WHERE a.user_id=?";
        return $this->execGetAll($sql, array($user_id));
    }


    /**
     * 查询所有佣金记录
     * @return array 为空的时候 说明查询成功 否则 为错误消息
     */
    function getAllBonus()
    {
        $sql = "select * from bonus_log;";
        return $this->execGetAll($sql);
    }


    /**
     * 通过用户ID查询用户消费记录
     * @param $userId 用户Id
     * @return array|mixed 为空的时候 说明查询成功 否则 为错误消息
     */
    function getUserExpend($userId)
    {
        $sql = "SELECT * FROM expend_log WHERE user_id=?";
        return $this->execGetAll($sql, array($userId));
    }

    /**
     * 联合用户消费日志与奖品 获取用户消费日志及得到奖品的信息
     * @param $user_id  用户ID
     * @return array 消费日志及奖品信息
     */
    function GetUserExpendAndPrize($user_id){
        $sql = "SELECT a.*,b.name,b.expend FROM expend_log a LEFT JOIN prize b ON a.prize_id=b.id WHERE user_id=?";
        return $this->execGetAll($sql, array($user_id));
    }


    /**
     * 通过ID 获取用户当月消费列表
     * @param $user_id 用户ID
     * @return array 用户当月消费列表
     */
    function GetUserMonthExpendListById($user_id){
        $sql="select * from expend_log where date_format(create_time,'%Y-%m')=date_format(now(),'%Y-%m') AND user_id=? ";
        return $this->execGetAll($sql, array($user_id));
    }

    /**
     * 通过用户ID 获取用户当月总消费
     * @param $user_id 用户ID
     * @return mixed int 当月总消费
     */
    function GetUserMonthExpendById($user_id){
        $sql="select SUM(money) AS sum from expend_log where date_format(create_time,'%Y-%m')=date_format(now(),'%Y-%m') AND user_id=? ";
        return $this->execGetFirst($sql, array($user_id))["sum"];
    }

    /**
     * 联合用户与消费日志与佣金日志 按页获取 用户当月总消费 当月总佣金
     * @param $page 页数 20条每页
     * @return array 用户信息及 用户当月总消费 当月总佣金
     */
    function GetUsersAndMonthExpend($page){
        $sql="
            select a.*,b.sum as mBonus 
            from (
                select a.*,b.sum AS mExpend
                from user a 
                LEFT JOIN (
                    SELECT user_id,sum(money) sum 
                    from expend_log 
                    where date_format(create_time,'%Y-%m')=date_format(now(),'%Y-%m') GROUP BY user_id) b 
                ON a.id=b.user_id ) a
            LEFT JOIN (
                SELECT user_id,sum(money) SUM 
                FROM bonus_log 
                where date_format(create_time,'%Y-%m')=date_format(now(),'%Y-%m') GROUP BY user_id) b 
            ON a.id=b.user_id 
            ORDER BY a.mExpend desc,a.expend desc ,b.sum desc,a.bonus desc
             LIMIT ".(20*($page-1)).",20
            ";
        return $this->execGetAll($sql);
    }

    private function WhenToWhen($when,$a){
        switch($when){
            case "m":
                $when="date_format(".$a."create_time,'%Y-%m')=date_format(now(),'%Y-%m')";
                break;
            case "lm":
                $when="date_format(".$a."create_time,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')";
                break;
            case "y":
                $when="date_format(".$a."create_time,'%Y')=date_format(now(),'%Y')";
                break;
            case "ly":
                $when="date_format(".$a."create_time,'%Y')=date_format(DATE_SUB(curdate(), INTERVAL 1 YEAR),'%Y')";
                break;
        }
        return $when;
    }





    /**
     * 获取 用户总数
     * @return mixed 用户总数
     */
    function GetUsersAndMonthExpendCount(){
        $sql="select count(*) as count from user";
        return $this->execGetFirst($sql,array())["count"];
    }

    /**
     * 查询所有消费记录
     * @return array 为空的时候 说明查询成功 否则 为错误消息
     */
    function getAllExpend()
    {
        $sql = "select * from expend_log;";
        return $this->execGetAll($sql);
    }

    function getAllExpendWhen($when)
    {
        $when=$this->WhenToWhen($when,"a.");
        $sql = "select
u.weixin_Id,u.name as user_name,u.phone,a.order_num,a.is_benefit,a.money,a.create_time,a.allow_barter,
a.finish_barter,p.name as prize_name,a.barter_time
from user u
LEFT JOIN expend_log a on a.user_id=u.id
LEFT JOIN prize p ON a.prize_id=p.id
where ".$when." order by a.create_time asc";
        return $this->execGetAll($sql);
    }

    function getAllExpendWhenTotal($when)
    {
        $when=$this->WhenToWhen($when,"a.");
        $sql = "select
sum(a.money) as total_money
from user u
LEFT JOIN expend_log a on a.user_id=u.id
LEFT JOIN prize p ON a.prize_id=p.id
where ".$when.";";
        return $this->execGetFirst($sql,array());
    }

    /**
     * 奖品兑换
     * @param $Id 消费记录Id
     * @param $prizeId 奖品Id
     * @return string 为空的时候 说明兑换成功 否则 为错误消息
     */
    function UpdateExpend_Prize($Id,$prizeId)
    {
        $prize = $this->getUserExpendById($Id);
        $prize["prize_id"] = $prizeId;
        $prize["finish_barter"] = true;
        return $this->setPrize($prize);
    }

    /**
     *通过消费记录ID查询用户消费记录
     * @param $Id 消费记录ID
     * @return array|mixed 为空的时候 说明查询成功 否则 为错误消息
     */
    function getUserExpendById($Id)
    {
        $sql = "SELECT * FROM expend_log WHERE id=?";
        return $this->execGetFirst($sql, array($Id));
    }

    /**
     * 奖品兑换时修改消费记录奖品信息
     * @param $prize 奖品信息
     * @return string 为空的时候 说明兑换成功 否则 为错误消息
     */
    private function setPrize($prize)
    {
        $sql = "UPDATE expend_log SET
                    `order_num`=:order_num,
                    `money`=:money,
                    `create_time`=:create_time,
                    `user_id`=:user_id,
                    `is_benefit`=:is_benefit,
                    `remark`=:remark,
                    `allow_barter`=:allow_barter,
                    `finish_barter`=:finish_barter,
                    `prize_id`=:prize_id,
                    `barter_time`=now()
                  WHERE id=:id;";
        unset($prize["barter_time"]);
        $result = $this->execSetSql($sql, $prize);
        return $result;
    }

    /**
     * 查询所有文章
     * @return array 为空的时候 说明查询成功 否则为错误信息
     */
    function GetAllArticle()
    {
        $sql = "select * from article";
        return $this->execGetAll($sql);
    }

    /**
     * 按类型获取文章
     * @param $type 文章类型
     * @return array 为空的时候 说明查询成功 否则为错误信息
     */
    function GetTypeArticle($type)
    {
        $sql = "select * from article where type=:type";
        return $this->execGetAll($sql, array("type" => $type));
    }

    /**
     *  按标签获取文章
     * @param $tag 文章标签
     * @return array|mixed 为空的时候 说明查询成功 否则为错误信息
     */
    function GetTagArticle($tag)
    {
        $sql = "select * from article where tag=:tag";
        return $this->execGetFirst($sql, array("tag" => $tag));
    }

    /**
     * 编辑文章内容
     * @param $details 文章内容
     * @param $tag 文章标签
     * @return string 为空的时候 说明更改成功 否则为错误信息
     */
    function SetArticle($details, $tag)
    {
        $sql = "update article set details=:details where tag=:tag";
        return $this->execSetSql($sql, array("details" => $details, "tag" => $tag));
    }

    /**
     * 获取所有分佣比例
     * @return array 为空的时候 说明查询成功 否则为错误信息
     */
    function GetAllRatio()
    {
        $sql = "select * from bonus_ratio";
        return $this->execGetAll($sql);
    }

    /**
     * 设置分佣层级的比例
     * @param $rank 分佣层级
     * @param $ratio 分佣比例
     * @return string 为空的时候 说明修改成功 否则为错误信息
     */
    function SetUserRatio($rank, $ratio)
    {
        $sql = "update bonus_ratio set ratio=:ratio where rank=:rank";
        return $this->execSetSql($sql, array("rank" => $rank, "ratio" => $ratio));
    }


    /**
     * 增加管理员
     * @param $weixin_id 微信id
     * @param $name 姓名
     * @param string $role 管理员等级
     * @return string 为空的时候 说明加入成功 否则为错误信息
     */
    function AddManage($weixin_id, $name, $role = Role::Ordinary)
    {
        $sql = "insert into manage(weixin_id,name,role) VALUES (:weixin,:name,:role)";
        return $this->execSetSql($sql, array("weixin" => $weixin_id, "name" => $name, "role" => $role));
    }


    /**
     * 获取所有管理员
     * @return array 为空的时候 说明查询成功 否则为错误信息
     */
    function GetAllManage()
    {
        $sql = "select * from manage";
        return $this->execGetAll($sql);
    }

    /**
     * 修改管理员
     * @param $oId 修改者id
     * @param $id 被修改者id
     * @param $name 管理员名字
     * @param $weixin_id 微信id
     * @param $role 管理员等级
     * @return string 为空的时候 说明修改成功 否则为错误信息
     */
    function SetManage($oId, $id, $name, $weixin_id, $role)
    {
        $manage = $this->GetManageById($oId);
        //var_export($manage);
        if ($manage["role"] == Role::SuperManager) {
            $where=array();
            $sql = "update manage set ";
            if (!empty($name)) {
                $sql = $sql . "  name=:name  ,";
                $where["name"]=$name;
            }
            if (!empty($weixin_id)) {
                $sql = $sql . "  weixin_id =:weixin_id ,";
                $where["weixin_id"]=$weixin_id;
            }
            if (!empty($role)) {
                $sql = $sql . " role =:role ,";
                $where["role"]=$role;
            }

            $sql = substr($sql, 0, -1);
            $sql = $sql . " where id=:id ";
            $where["id"]=$id;
            return $this->execSetSql($sql, $where);
        } else {
            return "对不起，你账号无权限";
        }
    }


    /**
     * @param $id 管理员id
     * @return array|mixed 为空的时候 说明查询成功 否则为错误信息
     */
    function GetManageById($id)
    {
        $sql = "select * from manage where id=:id";
        return $this->execGetFirst($sql, array("id" => $id));
    }

    /**
     * 通过微信ID获取管理员信息
     * @param $Wx_id 微信ID
     * @return array|mixed 管理员信息
     */
    function GetManageByWx_Id($Wx_id)
    {
        $sql = "select * from manage where weixin_id=:id";
        return $this->execGetFirst($sql, array("id" => $Wx_id));
    }

    /**
     * 模糊查询管理员
     * @param $search 关键字   匹配 微信号 名称 权限
     * @return array   匹配的管理员
     */
    function GetManagerBySearch($search){
        $sql = "select * from manage WHERE weixin_id LIKE :wd OR name LIKE :nm OR role LIKE :rl";
        return $this->execGetAll(
            $sql,
            array(
                "wd"=>"%".$search."%",
                "nm"=>"%".$search."%",
                "rl"=>"%".$search."%",
        ));
    }


    /**
     * 删除管理员
     * @param $oId 使用者id
     * @param $id 被删除者id
     * @return string 为空的时候 说明删除成功 否则为错误信息
     */
    function DelManage($oId, $id)
    {
        $user = $this->GetManageById($oId);
        if ($user["role"] == Role::SuperManager) {
            $sql = "delete from manage where id=:id";
            return $this->execSetSql($sql, array("id" => $id));
        } else {
            return "对不起，你账号无权限";
        }
    }
}

$core = new Core();
//$core->AddUser("admin", "123", null);
//$user = $core->GetUserByWxId("admin");
//$core->UserExpend($user["id"], "123", 5000, true, "没有备注");
//$core->UserExpend($user["id"], "456", 500, true, "没有备注aaa");
//var_export($core->GetPrizes());
//var_export($core->GetUserById(12));
//$core->UpdateUser(12, "123", "abc", "123");
//var_export($core->GetUserById(12));
//var_export($core->CloseUser(12));
//var_export($core->GetUserById(12));
//echo $core->AddUser("1231", "13320878691");

function Wx($postUrl){
    $ch = curl_init();//初始化curl
    curl_setopt($ch, CURLOPT_URL,$postUrl);//抓取指定网页
    curl_setopt($ch, CURLOPT_HEADER, 0);//设置header
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);//要求结果为字符串且输出到屏幕上

    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);//这个是重点。
    $data = curl_exec($ch);//运行curl
    curl_close($ch);
    return json_decode($data);
}

function Get_access_token(){
    $arr=require "/manage/temp/access_token.php";
    if($arr==1){
        $arr=array("","0");
    }
    if(time()-$arr[1]>7190){
        $postUrl = "https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=wx6a26f16011095354&secret=d007ae1d5fcd5d757684346b572a8d72";
        $Token=Wx($postUrl);
        $arr[0]=$Token->access_token;
        $arr[1]=time();
        file_put_contents("/manage/temp/access_token.php","<?php return ".var_export($arr, true)."; ?>",LOCK_EX);
    }
    return $arr[0];
}

function GetUserInfo($openId){
    $access_token=Get_access_token();
    $postUrl = "https://api.weixin.qq.com/cgi-bin/user/info?access_token=".$access_token."&openid=".$openId."&lang=zh_CN";
    return Wx($postUrl);
}

function GetTicket($state) {
    $token =Get_access_token();
    $postUrl = "https://api.weixin.qq.com/cgi-bin/qrcode/create?access_token=" . $token;
    $ch = curl_init();//初始化curl
    curl_setopt($ch, CURLOPT_URL, $postUrl);//抓取指定网页
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);//要求结果为字符串且输出到屏幕上
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);//这个是重点。

    curl_setopt($ch, CURLOPT_POST, 1);//post提交方式
    curl_setopt($ch, CURLOPT_POSTFIELDS, '{"expire_seconds": 604800, "action_name": "QR_SCENE", "action_info": {"scene": {"scene_id": '.$state.'}}}');
    curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json; charset=utf-8'));

    $result = curl_exec($ch);

    curl_close($ch);
    $arr=json_decode($result,true);
    if (isset($arr["errcode"])){
        file_put_contents("/manage/temp/access_token.php","",LOCK_EX);
        return GetTicket($state);
    }
    return $arr["ticket"];

}

function GetCodeImage($ticket){
    echo '<img src="https://mp.weixin.qq.com/cgi-bin/showqrcode?ticket='.$ticket.'">';
}


function SendMessage($user_id,$message) {
    global $core;
    $openId=$core->GetUserById($user_id)["weixin_Id"];
    $token =Get_access_token();
    $postUrl = "https://api.weixin.qq.com/cgi-bin/message/custom/send?access_token=" . $token;
    $ch = curl_init();//初始化curl
    curl_setopt($ch, CURLOPT_URL, $postUrl);//抓取指定网页
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);//要求结果为字符串且输出到屏幕上
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);//这个是重点。

    curl_setopt($ch, CURLOPT_POST, 1);//post提交方式
    curl_setopt($ch, CURLOPT_POSTFIELDS, '{
    "touser":"'.$openId.'",
    "msgtype":"text",
    "text":
    {
         "content":"'.$message.'"
    }
}');
    curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json; charset=utf-8'));

    $result = curl_exec($ch);
    //$arr=json_decode($result,true);

    curl_close($ch);
    return "";

}

function SendMessageToOpenId($openId,$message){
    $token =Get_access_token();
    $postUrl = "https://api.weixin.qq.com/cgi-bin/message/custom/send?access_token=" . $token;
    $ch = curl_init();//初始化curl
    curl_setopt($ch, CURLOPT_URL, $postUrl);//抓取指定网页
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);//要求结果为字符串且输出到屏幕上
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);//这个是重点。

    curl_setopt($ch, CURLOPT_POST, 1);//post提交方式
    curl_setopt($ch, CURLOPT_POSTFIELDS, '{
    "touser":"'.$openId.'",
    "msgtype":"text",
    "text":
    {
         "content":"'.$message.'"
    }
}');
    curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json; charset=utf-8'));

    $result = curl_exec($ch);

    curl_close($ch);
}